

using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
namespace GeneralClasses
{

    ///holds skill and keeps track of it in the database

    #region NOTES
    /*
     * 3 NOV 2010 - cleaning class, maing sure everything works. 
13 OCT 2010 - Created class, cant put it in so nothing is tested, still writing in notepadd++.
*/
    #endregion


    public class Skill
    {
        #region VARIABLES
        //==============================================================================
        //                                                                     VARIABLES
        //==============================================================================

        String name;
        int skillID;

        DatabaseConnection dbc;

        #endregion

        #region PROPERTIES
        //==============================================================================
        //                                                                    PROPERTIES
        //==============================================================================

       /* public String ConnectionString
        {
            get { return dbc.ConnectionString; }
            set
            {
                dbc.ConnectionString = value;
            }
        }*/

        public int SkillID
        {
            get { return skillID; }
            set { skillID = value; }
        }

        public String Name
        {
            get { return name; }
            set { name = value; }
        }

        #endregion

        #region CONSTRUCTOR
        //==============================================================================
        //                                                                   CONSTRUCTOR
        //==============================================================================

        public Skill()
        {
            skillID = -1;
            name = "Typing"; // default
            dbc = new DatabaseConnection();
        }

        #endregion

        #region PRIVATE

        //==============================================================================
        //                                                                       PRIVATE
        //==============================================================================

        private int removeSkillRelations()
        {
            try
            {
                if (skillID != -1)
                {
                    dbc.executeNonQuery("DELETE FROM shiftneedsskillrelationship WHERE sid='" + skillID + "'");
                    dbc.executeNonQuery("DELETE FROM departmentneedskillrelationship WHERE sid='" + skillID + "'");
                    dbc.executeNonQuery("DELETE FROM personhasskillrelationship WHERE sid='" + skillID + "'");
                    dbc.executeNonQuery("DELETE FROM paygradeneedsskillrelationship WHERE sid='" + skillID + "'");
                    return 0;
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        private bool checkSkillUsed()
        {
            try
            {
                if (this.skillID != -1)
                {
                    //shift
                    DataTable table = dbc.query("SELECT * FROM shiftneedsskillrelationship WHERE sid='" + this.skillID + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    //department
                    table = dbc.query("SELECT * FROM departmentneedskillrelationship WHERE sid='" + this.skillID + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    //person
                    table = dbc.query("SELECT * FROM personhasskillrelationship WHERE sid='" + this.skillID + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;

                    //paygrade
                    table = dbc.query("SELECT * FROM paygradeneedsskillrelationship WHERE sid='" + this.skillID + "'");
                    if ((table != null) && table.Rows.Count > 0) return true;
                }
                return false;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        //whether it exists or not in the database
        private bool checkSkillExists()
        {
            try
            {
                DataTable table = dbc.query("SELECT name FROM Skill WHERE name = '" + name + "'");
                return ((table != null) && table.Rows.Count > 0) ? (true) : (false);
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        private int findSkillID()
        {
            try
            {
                DataTable table = dbc.query("SELECT sid FROM Skill WHERE name = '" + name + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        return (int)row["sid"];
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        #endregion

        #region PUBLIC
        //==============================================================================
        //                                                                        PUBLIC
        //==============================================================================

        public static List<Skill> getAllSkills()
        {
            try
            {
                List<Skill> skillList = new List<Skill>();
                DatabaseConnection dbc = new DatabaseConnection();
                DataTable table = dbc.query("SELECT sid FROM Skill");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Skill skill = new Skill();
                        skill.skillID = (int)row["sid"];
                        skill.loadData();
                        skillList.Add(skill);
                    }
                }
                return skillList;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// saves the data. 
        /// </summary>
        public int saveData()
        {
            try
            {
                if (this.skillID == -1)
                {
                    if (!this.checkSkillExists())
                    {
                        //add
                        dbc.executeNonQuery("INSERT INTO Skill (name) values ('" + name + "')");
                    }


                    return (skillID = findSkillID());
                }
                else
                {
                    //save
                    dbc.executeNonQuery("UPDATE Skill SET name='" + name + "' WHERE sid='" + this.skillID + "'");
                    return (skillID = findSkillID());
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public static Skill loadData(int skillID)
        {
            Skill skill = new Skill();
            skill.SkillID = skillID;
            skill.loadData();
            return skill;           
        }

        public void loadData()
        {
            try
            {
                DataTable table = dbc.query("SELECT name FROM Skill WHERE sid = '" + this.skillID + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        name = (string)row["name"];
                    }
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public int removeData()
        {
            try
            {
                if (this.skillID != -1)
                {
                    removeSkillRelations();
                    if (!this.checkSkillUsed())
                    {
                        dbc.executeNonQuery("DELETE FROM Skill WHERE sid='" + this.skillID + "'");
                        return 0;
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        public void deprecated_deleteSkill() { }
        public void deprecated_addSkill() { }
        public int deprecated_getSkillID() { return -1; }

        #endregion

    } // end class


} //end namespace